package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.CourseInfo;

public class Course extends Database{

	private String courseNumber;
	private String courseName;
	private int termYear;
	private String sectionNumber;
	private String term;
	private String instructor;
	
	public Course()
	{
		tableName = "Course";	
	}
	
	public Course(String courseNumber, String courseName, String sectionNumber, int termYear, String term, String instructor)
	{
		tableName = "Course";
		
		this.courseNumber = courseNumber;
		this.courseName = courseName;
		this.sectionNumber = sectionNumber;
		this.termYear = termYear;
		this.term = term;
		this.instructor = instructor;
	}
	@Override
	public void Insert() throws SQLException {
		
		Statement stmt = connection.createStatement();
		
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" +
	    					AddQuotes(courseNumber) + ", " +			
	    					AddQuotes(courseName) + ", " +
	    					AddQuotes(sectionNumber) + ", " +
	    					term + ", " +
	    					AddQuotes(term) + ", " +
	    					AddQuotes(instructor)  +
	    					")";
	    stmt.executeUpdate(sqlString);
	    System.out.println(sqlString);
	}

	//Editted by Steven Chow. There are 2 primary keys here! must have 2 parameters
	public void Delete(String courseNumber, String sectionNumber) throws SQLException {
		
		Statement stmt = connection.createStatement();
		String sqlString = "DELETE from " + tableName + " WHERE courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber= " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
	}

	@Override
	public void Display() throws SQLException {
		// TODO Auto-generated method stub
		
	}

	//Created by Steven Chow
	public ArrayList<CourseInfo> GetAllCourseInfo() throws SQLException
	{
		ArrayList<CourseInfo> list = new ArrayList<CourseInfo>();
		String sqlString =  "select * from " + tableName;
		
		
	    Statement stmt = connection.createStatement();
	    
	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())//get flight info
	    {
	    	
	    	CourseInfo info = new CourseInfo();
	    	
			info.courseNumber = rs.getString("courseNumber");
			info.courseName = rs.getString("courseName");
			info.sectionNumber = rs.getString("sectionNumber");
			info.termYear = rs.getInt("termYear");
			info.term = rs.getString("term");
			info.instructor = rs.getString("instructor");
	    	list.add(info);
	    }
	    
	    return list;
	}
	
	//Created by steven Chow
	public ArrayList<String> GetCourseInfo(String courseNumber, String sectionNumber) throws SQLException
	{
		ArrayList<String> CourseInfo = new ArrayList<String>();
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + tableName + " where courseNumber = " + AddQuotes(courseNumber) + "AND sectionNumber = "
				+ AddQuotes(sectionNumber);
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		
		while(rs.next())//check if user exists in databse
		{
			this.courseNumber = rs.getString("courseNumber");
			this.courseName = rs.getString("courseName");
			this.sectionNumber = rs.getString("sectionNumber");
			//this.termYear = 2000;
			this.termYear = rs.getInt("termYear");
			this.term = rs.getString("term");
			String tYear = Integer.toString(this.termYear);
			this.instructor = rs.getString("instructor");
			
			CourseInfo.add(this.courseName);
			CourseInfo.add(this.term);
			CourseInfo.add(tYear);
			CourseInfo.add(this.instructor);
		}
		
		return CourseInfo; 
		
	}

	//Nothing to impletement here
	@Override
	public void Delete(String primaryKey) throws SQLException {
		// TODO Auto-generated method stub
		
	}
	
}
